package cn.devil.two.reportFormStatistics

import cn.devil.two.utils.{CheckArguments, SCUtiles}
import org.apache.spark.SparkContext
import org.apache.spark.sql.{DataFrame, SQLContext}

object SQLBusinessLogic {
  def main(args: Array[String]): Unit = {
    CheckArguments(args,1,"请输入读取数据的文件",this.getClass)
    val Array(inputFilePath) = args
    val sc: SparkContext = SCUtiles.createSparkContextInstance("SQL业务")
    val sQLContext: SQLContext = SCUtiles.createSQLContextInstance(sc)
    val dFrame = sQLContext.read.parquet(inputFilePath)
    dFrame.registerTempTable("leopard_temporary")
    val primordial: DataFrame = sQLContext.sql("select provincename, cityname,count(1) 原始 from leopard_temporary where requestmode = 1 and processnode>= 1 group by provincename, cityname ")
    primordial.registerTempTable("primordial")
    val valid = sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where requestmode = 1 and processnode>= 2 group by provincename, cityname ")
    valid.registerTempTable("valid")
    val advertising = sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where requestmode = 1 and processnode =3 group by provincename, cityname ")
    advertising.registerTempTable("advertising")
   val participation =  sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where iseffective = 1 and isbilling=1 and isbid=1 and  adorderid !=0 group by provincename, cityname ")
    participation.registerTempTable("participation")
    val success = sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where iseffective = 1 and  isbilling=1  and   iswin = 1 group by provincename, cityname ")
    success.registerTempTable("success")
    val show =  sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where requestmode = 2 and    iseffective = 1 group by provincename, cityname ")
    show.registerTempTable("show")
    val click = sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where requestmode = 3 and  iseffective = 1 group by provincename, cityname ")
    click.registerTempTable("click")
    val DSPconsumption= sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where iseffective = 1  and isbilling=1   and  iswin = 1 group by provincename, cityname ")
    DSPconsumption.registerTempTable("DSPconsumption")
    val DSPcost =sQLContext.sql("select provincename, cityname,count(1) 有效 from leopard_temporary where iseffective = 1 and  isbilling=1   and  iswin = 1 group by provincename, cityname ")
    DSPcost.registerTempTable("DSPcost")
    //primordial,valid,advertising,participation,success,show,click,DSPconsumption,DSPcost
    sQLContext.sql("create table fzh select * from primordial union select * from valid")

    sc.stop()


  }
}
